package cn.jhc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.mysql.cj.jdbc.MysqlDataSource;

import cn.jhc.entity.Article;
import cn.jhc.entity.User;



public class Dao {
    //单例模式
    private static MysqlDataSource dataSource = null;
    
    private static final String LOGIN_INSERT_SQL = "insert into user (username, password) values(?, sha(?))";
    private static final String LOGIN_SELECT_SQL = "select id from user where username = ? and password = sha(?)";
    private static final String ADD_ARTICLE_INSERT_SQL = "insert into article (id, content, date, refilename) value(?, ?, now(), ?)";
    private static final String GET_ARTICLE_SELECT_SQL = "select aid, id, content, date, refilename from article";
    private static final String GET_USER_SELECT_SQL = "select id, username, password from user where username = ";


    // private static final String SELECT_SQL = "select id, name, sex, hobbies, origin, dest, ctype from survey";
    // private static final String REMOVE_SQL = "delete from survey where id = ?";

    public synchronized static DataSource getDataSource() {
        if( dataSource == null) {
            dataSource = new MysqlDataSource();
            dataSource.setURL("jdbc:mysql://localhost/server_test?user=suuser&password=123456&zeroDateTimeBehavior = convertToNull");
        }
        return dataSource;
    }

    public User getUser(String username) throws SQLException {
        User user = new User();
        try(Connection connection = getDataSource().getConnection();
            Statement stat = connection.createStatement();
            ResultSet rs = stat.executeQuery("select id, username, password from user where username = " + username) ) {
            while (rs.next()) {
                user.setId(rs.getInt(1));
                user.setUsername(rs.getString(2));
                user.setPassword(rs.getString(3));
                
                
            }
        }
        System.out.println(user);
        return user;
        

    }

    public String getName(int id) throws SQLException {
        String username = "null";
        try(Connection connection = getDataSource().getConnection();
            Statement stat = connection.createStatement();
            ResultSet rs = stat.executeQuery("select username from user where id = " + id) ) {
            while (rs.next()) {
                username = rs.getString(1);  
            }
        }
        System.out.println(username);
        return username;
        

    }
    public int getId(String username) throws SQLException {
        int id = 1;
        try(Connection connection = getDataSource().getConnection();
            Statement stat = connection.createStatement();
            ResultSet rs = stat.executeQuery("select id from user where username = '" + username + "'") ) {
            while (rs.next()) {
                id = rs.getInt(1);  
            }
        }
        System.out.println(username);
        return id;
        

    }
    public void register(User user) throws SQLException {
        try (Connection connection = getDataSource().getConnection();
            PreparedStatement stat = connection.prepareStatement(LOGIN_INSERT_SQL)) {
            stat.setString(1, user.getUsername());
            stat.setString(2, user.getPassword());
            
            stat.executeUpdate();
        } 
    }

    public boolean login(String username, String password) throws SQLException {
        boolean flag = false;
        try(Connection connection = getDataSource().getConnection();
            PreparedStatement stat = connection.prepareStatement(LOGIN_SELECT_SQL)) {
                stat.setString(1, username);
                stat.setString(2, password);
                
                ResultSet rs = stat.executeQuery();
                // System.out.println(rs.next());
                if (rs.next()) {
                    flag = true;
                }
        }
        return flag;
        
    }

    public void addArticle(Article article) throws SQLException {
        try(Connection connection = getDataSource().getConnection();
                PreparedStatement stat = connection.prepareStatement(ADD_ARTICLE_INSERT_SQL)) {
                    stat.setInt(1, article.getId());
                    stat.setString(2, article.getContent());
                    stat.setString(3, article.getReFilename());
                    stat.executeUpdate();
                }       

    }
    public List<Article> findAllArticle() throws SQLException {
    List<Article> articles = new ArrayList<>();
        try(Connection connection = getDataSource().getConnection();
            Statement stat = connection.createStatement();
            ResultSet rs = stat.executeQuery(GET_ARTICLE_SELECT_SQL)) {
            while (rs.next()) {
                Article article = new Article();
                article.setAid(rs.getInt(1));
                article.setId(rs.getInt(2));
                article.setContent(rs.getString(3));
                article.setDate(rs.getTimestamp(4));
                article.setReFilename(rs.getString(5));
                article.setUsername(getName(rs.getInt(2)));
                articles.add(article);
            }
        }
        System.out.println(articles);
        return articles;
    }

    // public List<User> findAllItems() throws SQLException {
    //     List<User> users = new ArrayList<>();
    //     try(Connection connection = getDataSource().getConnection();
    //         Statement stat = connection.createStatement();
    //         ResultSet rs = stat.executeQuery(SELECT_SQL)) {
    //         while (rs.next()) {
    //             Item item = new Item();
    //             item.setId(rs.getInt(1));
    //             item.setName(rs.getString(2));
    //             item.setSex(rs.getString(3));
    //             item.setHobbies(rs.getString(4));
    //             item.setOriginFilename(rs.getString(5));
    //             item.setReFilename(rs.getString(6));
    //             item.setCtype(rs.getString(7));
    //             items.add(item);
    //         }
    //     }
    //     return items;

    // }

    // public void remove(int id) throws SQLException {
    //     try(Connection connection = getDataSource().getConnection();
    //         PreparedStatement stat = connection.prepareStatement(REMOVE_SQL)) {
    //             stat.setInt(1, id);
    //             stat.executeUpdate();
    //         }
    // }

    
}
